package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.db.DBConn;
import dao.HomeworkMagDao;
import domain.Homework;
import domain.LearnRes;

/**
 * 
 * Jun 4, 2009
 *
 * @author Fabian
 *
 */
public class HomeworkMagImpl implements HomeworkMagDao {

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#addBookHomework(vo.Homework)
	 */
	public boolean addBookHomework(Homework hw) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//计算应该交作业的人数
		String sql0 = "select count(*) from t_student where class_id = ?";
		//根据id获取年级信息
		String sql1 = "select grade from t_grade where id = ?";
		String sql = "insert into t_homework (title, content, sort, pub_user, pub_time, sub_time, grade, class_id, need_count, sub_count, correct_count) values (?, ?, ?, ?, now(), ?, ?, ?, ?, 0, 0)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql0);
			pstmt.setInt(1, Integer.parseInt(hw.getBanji()));
			rs = pstmt.executeQuery();		
			if (rs.next()) {
				int need_count = rs.getInt(1);
				
				pstmt = DBConn.getPstmt(conn, sql1);
				pstmt.setInt(1, Integer.parseInt(hw.getGrade()));
				rs = pstmt.executeQuery();		
				if (rs.next()) {
					int grade = rs.getInt(1);
								
					pstmt = DBConn.getPstmt(conn, sql);
					pstmt.setString(1, hw.getTitle());
					pstmt.setString(2, hw.getContent());
					pstmt.setString(3, hw.getSort());
					pstmt.setString(4, hw.getPub_user());
					pstmt.setDate(5, new java.sql.Date(hw.getSub_time().getTime()));
					pstmt.setInt(6, grade);
					pstmt.setInt(7, Integer.parseInt(hw.getBanji()));
					pstmt.setInt(8, need_count);
					int rsColum = pstmt.executeUpdate();		
					if (rsColum > 0) {
						result = true;
						return result;
					}
				} else {
					return result;
				}
			} else {
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#addGoodHomework(vo.Homework)
	 */
	
	public boolean addGoodHomework(Homework hw) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//计算应该交作业的人数
		String sql0 = "select count(*) from t_student where class_id = ?";
		//根据id获取年级信息
		String sql1 = "select grade from t_grade where id = ?";
		String sql = "insert into t_homework values (null, ?, ?, ?, ?, ?, ?, ?, now(), ?, ?, ?, ?, 0, 0)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql0);
			pstmt.setInt(1, Integer.parseInt(hw.getBanji()));
			rs = pstmt.executeQuery();		
			if (rs.next()) {
				int need_count = rs.getInt(1);
				
				pstmt = DBConn.getPstmt(conn, sql1);
				pstmt.setInt(1, Integer.parseInt(hw.getGrade()));
				rs = pstmt.executeQuery();		
				if (rs.next()) {
					int grade = rs.getInt(1);
								
					pstmt = DBConn.getPstmt(conn, sql);
					pstmt.setString(1, hw.getTitle());
					pstmt.setString(2, hw.getContent());
					pstmt.setString(3, hw.getFormat());
					pstmt.setString(4, hw.getPath());
					pstmt.setString(5, hw.getSort());
					pstmt.setString(6, hw.getSize());
					pstmt.setString(7, hw.getPub_user());
					pstmt.setDate(8, new java.sql.Date(hw.getSub_time().getTime()));
					pstmt.setInt(9, grade);
					pstmt.setInt(10, Integer.parseInt(hw.getBanji()));
					pstmt.setInt(11, need_count);
					int rsColum = pstmt.executeUpdate();		
					if (rsColum > 0) {
						result = true;
						return result;
					}
				} else {
					return result;
				}
			} else {
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#delHomework(int)
	 */
	
	public boolean delHomework(int id) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql1 = "delete from t_outcome where hw_id = ?";
		String sql2 = "delete from t_homework where id = ?";
		try {
			conn = DBConn.getConn();
			conn.setAutoCommit(false);
			pstmt = DBConn.getPstmt(conn, sql1);
			pstmt.setInt(1, id);
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				pstmt = DBConn.getPstmt(conn, sql2);
				pstmt.setInt(1, id);
				int rsColum2 = pstmt.executeUpdate();		
				if (rsColum2 > 0) {
					conn.commit();
					result = true;
					return result;
				} else {
					conn.rollback();
				}						
			} else {
				conn.rollback();
			}
		} catch(SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return result;
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#getStuBookTotalCount(int)
	 */
	
	public int getStuBookTotalCount(int stu_id) {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_homework where sort = 'bookHomework' and class_id = (select class_id from t_student where id = ?)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, stu_id);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#getStuGoodTotalCount(int)
	 */
	
	public int getStuGoodTotalCount(int stu_id) {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_homework where sort = 'goodHomework' and class_id = (select class_id from t_student where id = ?)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, stu_id);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#getStuPageBookHomework(int, int, int)
	 */
	
	public List<Homework> getStuPageBookHomework(int start, int limit,
			int stu_id) {
		List<Homework> list = new ArrayList<Homework>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String sql = "select * from t_homework where sort = 'bookHomework' and class_id = (select class_id from t_student where id = ?) order by pub_time desc limit ?, ?";
		//查看学生是否已经完成此作业
		String sql2 = "select count(*) as count from t_outcome where hw_id = ? and stu_id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, stu_id);
			pstmt.setInt(2, start);
			pstmt.setInt(3, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Homework hw = new Homework();
				int hw_id = rs.getInt("id");
				
				pstmt = DBConn.getPstmt(conn, sql2);
				pstmt.setInt(1, hw_id);
				pstmt.setInt(2, stu_id);
				rs2 = pstmt.executeQuery();
				int resultCount = 0;
				if (rs2.next()) resultCount = rs2.getInt(1);
				if (resultCount > 0) {
					hw.setIf_done("已经完成");
				} 		
				
				hw.setId(hw_id);
				hw.setTitle(rs.getString("title"));
				hw.setContent(rs.getString("content"));
				hw.setGrade(rs.getString("grade"));
				hw.setBanji(rs.getString("class_id"));
				hw.setPub_user(rs.getString("pub_user"));
				hw.setPub_time(rs.getTimestamp("pub_time"));
				hw.setSub_time(rs.getTimestamp("sub_time"));
				hw.setNeed_count(rs.getInt("need_count"));
				hw.setSub_count(rs.getInt("sub_count"));
				hw.setCorrect_count(rs.getInt("correct_count"));				
				list.add(hw);
			}
			rs2.close();
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#getStuPageGoodHomework(int, int, int)
	 */
	
	public List<Homework> getStuPageGoodHomework(int start, int limit,
			int stu_id) {
		List<Homework> list = new ArrayList<Homework>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String sql = "select * from t_homework where sort = 'goodHomework' and class_id = (select class_id from t_student where id = ?) order by pub_time desc limit ?, ?";
		//查看学生是否已经完成此作业
		String sql2 = "select count(*) as count from t_outcome where hw_id = ? and stu_id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, stu_id);
			pstmt.setInt(2, start);
			pstmt.setInt(3, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Homework hw = new Homework();
				int hw_id = rs.getInt("id");
				
				pstmt = DBConn.getPstmt(conn, sql2);
				pstmt.setInt(1, hw_id);
				pstmt.setInt(2, stu_id);
				rs2 = pstmt.executeQuery();
				int resultCount = 0;
				if (rs2.next()) resultCount = rs2.getInt(1);
				if (resultCount > 0) {
					hw.setIf_done("已经完成");
				} 		
				
				hw.setId(hw_id);
				hw.setTitle(rs.getString("title"));
				hw.setContent(rs.getString("content"));
				hw.setFormat(rs.getString("format"));
				hw.setPath(rs.getString("path"));
				hw.setSize(rs.getString("size"));
				hw.setGrade(rs.getString("grade"));				
				hw.setBanji(rs.getString("class_id"));
				hw.setPub_user(rs.getString("pub_user"));
				hw.setPub_time(rs.getTimestamp("pub_time"));
				hw.setSub_time(rs.getTimestamp("sub_time"));
				hw.setNeed_count(rs.getInt("need_count"));
				hw.setSub_count(rs.getInt("sub_count"));
				hw.setCorrect_count(rs.getInt("correct_count"));				
				list.add(hw);
			}
			rs2.close();
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#modifyHomework(vo.Homework)
	 */
	
	public boolean modifyHomework(Homework hw) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//计算应该交作业的人数
		String sql0 = "select count(*) from t_student where class_id = ?";
		//根据id获取年级信息
		String sql1 = "select grade from t_grade where id = ?";
		String sql = "update t_homework set title=?, content=?, sub_time=?, grade=?, class_id=?, need_count=? where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql0);
			pstmt.setInt(1, Integer.parseInt(hw.getBanji()));
			rs = pstmt.executeQuery();		
			if (rs.next()) {
				int need_count = rs.getInt(1);
				
				pstmt = DBConn.getPstmt(conn, sql1);
				pstmt.setInt(1, Integer.parseInt(hw.getGrade()));
				rs = pstmt.executeQuery();		
				if (rs.next()) {
					int grade = rs.getInt(1);
								
					pstmt = DBConn.getPstmt(conn, sql);
					pstmt.setString(1, hw.getTitle());
					pstmt.setString(2, hw.getContent());
					pstmt.setDate(3, new java.sql.Date(hw.getSub_time().getTime()));
					pstmt.setInt(4, grade);
					pstmt.setInt(5, Integer.parseInt(hw.getBanji()));
					pstmt.setInt(6, need_count);
					pstmt.setInt(7, hw.getId());
					int rsColum = pstmt.executeUpdate();		
					if (rsColum > 0) {
						result = true;
						return result;
					}
				} else {
					return result;
				}
			} else {
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#getPageBookHomework(int, int, int)
	 */
	
	public List<Homework> getPageBookHomework(int start, int limit, int tch_id) {
		List<Homework> list = new ArrayList<Homework>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from t_homework where sort = 'bookHomework' and pub_user = (select name from t_teacher where id = ?) order by pub_time desc limit ?, ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, tch_id);
			pstmt.setInt(2, start);
			pstmt.setInt(3, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Homework hw = new Homework();
				hw.setId(rs.getInt("id"));
				hw.setTitle(rs.getString("title"));
				hw.setContent(rs.getString("content"));
				hw.setGrade(rs.getString("grade"));
				hw.setBanji(rs.getString("class_id"));
				hw.setPub_user(rs.getString("pub_user"));
				hw.setPub_time(rs.getTimestamp("pub_time"));
				hw.setSub_time(rs.getTimestamp("sub_time"));
				hw.setNeed_count(rs.getInt("need_count"));
				hw.setSub_count(rs.getInt("sub_count"));
				hw.setCorrect_count(rs.getInt("correct_count"));				
				list.add(hw);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#getPageGoodHomework(int, int, int)
	 */
	
	public List<Homework> getPageGoodHomework(int start, int limit, int tch_id) {
		List<Homework> list = new ArrayList<Homework>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from t_homework where sort = 'goodHomework' and pub_user = (select name from t_teacher where id = ?) order by pub_time desc limit ?, ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, tch_id);
			pstmt.setInt(2, start);
			pstmt.setInt(3, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Homework hw = new Homework();
				hw.setId(rs.getInt("id"));
				hw.setTitle(rs.getString("title"));
				hw.setContent(rs.getString("content"));
				hw.setFormat(rs.getString("format"));
				hw.setPath(rs.getString("path"));
				hw.setSize(rs.getString("size"));
				hw.setGrade(rs.getString("grade"));				
				hw.setBanji(rs.getString("class_id"));
				hw.setPub_user(rs.getString("pub_user"));
				hw.setPub_time(rs.getTimestamp("pub_time"));
				hw.setSub_time(rs.getTimestamp("sub_time"));
				hw.setNeed_count(rs.getInt("need_count"));
				hw.setSub_count(rs.getInt("sub_count"));
				hw.setCorrect_count(rs.getInt("correct_count"));				
				list.add(hw);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#getBookTotalCount(int)
	 */
	
	public int getBookTotalCount(int tch_id) {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_homework where sort = 'bookHomework' and pub_user = (select name from t_teacher where id = ?)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, tch_id);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see dao.HomeworkMagDao#getGoodTotalCount(int)
	 */
	
	public int getGoodTotalCount(int tch_id) {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_homework where sort = 'goodHomework' and pub_user = (select name from t_teacher where id = ?)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, tch_id);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

}
